package com.example.demo;

import java.sql.*;
import java.util.*;

/**
 * 数据库助手类，提供数据库操作相关功能
 */
public class DatabaseHelper {
    private static final String URL = "jdbc:mysql://47.120.11.196:3306/fafhub?useUnicode=true&characterEncoding=utf8&zeroDateTimeBehavior=convertToNull&useSSL=false&serverTimezone=GMT%2B8&allowPublicKeyRetrieval=true";
    private static final String USERNAME = "fafhub";
    private static final String PASSWORD = "fafhub123";
    
    // 页面对应的表名
    private static final Map<String, String> PAGE_TABLES = new HashMap<>();
    
    // 表创建状态标志，防止重复创建表
    private static final Map<String, Boolean> TABLE_CREATED = new HashMap<>();
    
    // 数据库检查标志，避免重复检查
    private static boolean DATABASE_CHECKED = false;
    
    static {
        PAGE_TABLES.put("page1", "reagent_list");  // 修改为试剂列表表名
        PAGE_TABLES.put("page2", "page2_data");
        PAGE_TABLES.put("page3", "page3_data");
        PAGE_TABLES.put("page4", "page4_data");
        
        // 初始化表创建状态
        TABLE_CREATED.put("reagent_list", false);
        TABLE_CREATED.put("page2_data", false);
        TABLE_CREATED.put("page3_data", false);
        TABLE_CREATED.put("page4_data", false);
    }
    
    /**
     * 获取数据库连接
     */
    public static Connection getConnection() throws SQLException, ClassNotFoundException {
        Class.forName("com.mysql.cj.jdbc.Driver");
        
        // 首先确保数据库存在
        ensureDatabaseExists();
        
        return DriverManager.getConnection(URL, USERNAME, PASSWORD);
    }
    
    /**
     * 确保数据库存在，如果不存在则创建
     */
    private static void ensureDatabaseExists() throws SQLException, ClassNotFoundException {
        // 如果已经检查过，直接返回
        if (DATABASE_CHECKED) {
            return;
        }
        
        
        // 连接到MySQL服务器（不指定数据库）
        String serverUrl = "jdbc:mysql://47.120.11.196:3306/?useUnicode=true&characterEncoding=utf8&zeroDateTimeBehavior=convertToNull&useSSL=false&serverTimezone=GMT%2B8&allowPublicKeyRetrieval=true";
        
        try (Connection conn = DriverManager.getConnection(serverUrl, USERNAME, PASSWORD)) {
            // 检查数据库是否存在
            String checkDbSql = "SELECT SCHEMA_NAME FROM INFORMATION_SCHEMA.SCHEMATA WHERE SCHEMA_NAME = 'fafhub'";
            try (PreparedStatement stmt = conn.prepareStatement(checkDbSql);
                 ResultSet rs = stmt.executeQuery()) {
                
                if (!rs.next()) {
                        // 数据库不存在，创建它
                        try (Statement createStmt = conn.createStatement()) {
                            createStmt.execute("CREATE DATABASE fafhub DEFAULT CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci");
                        }
                    }
            }
            
            // 标记数据库已检查
            DATABASE_CHECKED = true;
            
        } catch (SQLException e) {
            e.printStackTrace();
            throw e;
        }
    }
    
    /**
     * 测试数据库连接和基本操作权限
     */
    public static void testDatabaseConnection() throws SQLException, ClassNotFoundException {
        try (Connection conn = getConnection()) {
            // 测试基本查询权限
            try (Statement stmt = conn.createStatement()) {
                ResultSet rs = stmt.executeQuery("SELECT 1");
            }
            
            // 测试创建表权限
            try (Statement stmt = conn.createStatement()) {
                stmt.execute("CREATE TABLE IF NOT EXISTS test_permissions (id INT)");
                
                // 清理测试表
                stmt.execute("DROP TABLE IF EXISTS test_permissions");
            }
        } catch (Exception e) {
            e.printStackTrace();
            throw e;
        }
    }
    
    /**
     * 检查表是否存在，不存在则创建
     */
    public static boolean checkAndCreateTable(String page) throws SQLException, ClassNotFoundException {
        String tableName = PAGE_TABLES.getOrDefault(page, null);
        if (tableName == null) {
            return false; // 无效的页面名称
        }
        
        
        // 首次运行时测试数据库连接
        if (!DATABASE_CHECKED) {
            testDatabaseConnection();
        }
        
        try (Connection conn = getConnection()) {
            // 使用更直接的方式检查表是否存在
            String checkTableSql = "SELECT COUNT(*) FROM information_schema.tables WHERE table_schema = 'fafhub' AND table_name = ?";
            try (PreparedStatement stmt = conn.prepareStatement(checkTableSql)) {
                stmt.setString(1, tableName);
                try (ResultSet rs = stmt.executeQuery()) {
                        if (rs.next() && rs.getInt(1) > 0) {
                            // 表已存在
                            TABLE_CREATED.put(tableName, true);
                            return true;
                        } else {
                            // 表不存在，创建表
                            boolean created = createTable(conn, tableName);
                            if (created) {
                                TABLE_CREATED.put(tableName, true);
                                return true;
                            } else {
                                return false;
                            }
                        }
                }
            }
        } catch (Exception e) {
            e.printStackTrace();
            throw e;
        }
    }
    
    /**
     * 创建表
     */
    private static boolean createTable(Connection conn, String tableName) throws SQLException {
        String createTableSQL;
        
        switch (tableName) {
            case "reagent_list":
                createTableSQL = "CREATE TABLE " + tableName + " ("
                    + "id INT AUTO_INCREMENT PRIMARY KEY, "
                    + "supplier VARCHAR(100) NOT NULL, "         // 供应商
                    + "material_code VARCHAR(50) NOT NULL, "     // 物料编码
                    + "material_name VARCHAR(100) NOT NULL, "    // 物料名称
                    + "specification VARCHAR(100), "             // 规格型号
                    + "unit VARCHAR(20), "                       // 单位
                    + "quantity INT, "                           // 数量
                    + "batch_number VARCHAR(50), "               // 批次
                    + "arrival_date DATE, "                      // 到货日期
                    + "clone_number VARCHAR(20), "               // 克隆号
                    + "expiry_date DATE, "                       // 到期日期
                    + "user_id VARCHAR(100), "                   // 用户ID
                    + "create_time TIMESTAMP DEFAULT CURRENT_TIMESTAMP, "
                    + "update_time TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP"
                    + ")";
                break;
                
            case "page2_data":
                createTableSQL = "CREATE TABLE " + tableName + " ("
                    + "id INT AUTO_INCREMENT PRIMARY KEY, "
                    + "use_time DATE NOT NULL, "               // 时间
                    + "material_code VARCHAR(50) NOT NULL, "    // 实验号(对应page1的物料编码)
                    + "group_names VARCHAR(500), "              // 分组(多选)，改为group_names避免使用MySQL保留字
                    + "details TEXT, "                          // 详细项目
                    + "user_id VARCHAR(100), "                  // 用户ID
                    + "create_time TIMESTAMP DEFAULT CURRENT_TIMESTAMP, "
                    + "update_time TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP"
                    + ")";
                break;
                
            case "page3_data":
                createTableSQL = "CREATE TABLE " + tableName + " ("
                    + "id INT AUTO_INCREMENT PRIMARY KEY, "
                    + "product_name VARCHAR(100) NOT NULL, "
                    + "price DECIMAL(10,2), "
                    + "inventory INT DEFAULT 0, "
                    + "category VARCHAR(50), "
                    + "create_time TIMESTAMP DEFAULT CURRENT_TIMESTAMP, "
                    + "update_time TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP"
                    + ")";
                break;
                
            case "page4_data":
                createTableSQL = "CREATE TABLE " + tableName + " ("
                    + "id INT AUTO_INCREMENT PRIMARY KEY, "
                    + "username VARCHAR(50) NOT NULL, "
                    + "email VARCHAR(100), "
                    + "phone VARCHAR(20), "
                    + "address TEXT, "
                    + "create_time TIMESTAMP DEFAULT CURRENT_TIMESTAMP, "
                    + "update_time TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP"
                    + ")";
                break;
                
            default:
                throw new SQLException("无效的表名: " + tableName);
        }
        
        try (Statement stmt = conn.createStatement()) {
            stmt.execute(createTableSQL);
            return true;
        } catch (SQLException e) {
            e.printStackTrace();
            return false;
        }
    }
    
    /**
     * 添加示例数据
     */
    private static void addSampleData(Connection conn, String tableName) throws SQLException {
        String insertSQL;
        
        switch (tableName) {
            case "reagent_list":
                insertSQL = "INSERT INTO " + tableName + " (supplier, material_code, material_name, specification, unit, quantity, batch_number, arrival_date, clone_number, expiry_date) VALUES "
                    + "('河南赛诺特生物技术有限公司', '01.14.0029.10041.00001', 'p63', '6ml', '支', 1, '1', '2025-04-01', 'A', '2026-09-01'), "
                    + "('河南赛诺特生物技术有限公司', '01.14.0029.10083.00001', 'CK5/6', '6ml', '支', 1, '1', '2025-05-01', 'A', '2026-09-02'), "
                    + "('河南赛诺特生物技术有限公司', '01.14.0029.10120.00002', 'Syn', '3ml', '支', 1, '1', '2025-05-02', 'A', '2026-09-03'), "
                    + "('北京中杉金桥生物技术有限公司', '01.14.0022.10128.00002', 'Calretinin', '3ml', '支', 1, '1', '2025-04-21-80', 'A', '2026-09-04'), "
                    + "('北京中杉金桥生物技术有限公司', '01.14.0022.10041.00004', 'P504s', '6ml', '支', 1, '1', '2025-04-23-19', 'A', '2026-09-05'), "
                    + "('北京中杉金桥生物技术有限公司', '01.14.0022.10051.00002', 'GATA3', '3ml', '支', 1, '1', '2025-04-20-07', 'A', '2026-09-06')";
                break;
                
            case "page2_data":
                insertSQL = "INSERT INTO " + tableName + " (use_time, material_code, group_names, details) VALUES "
                    + "('2025-04-15', '01.14.0029.10041.00001', '乳腺,宫颈', '样本1的详细项目描述'), "
                    + "('2025-04-16', '01.14.0029.10083.00001', '前列腺,甲状腺', '样本2的详细项目描述'), "
                    + "('2025-04-17', '01.14.0022.10041.00004', '神经内分泌,细胞蜡块', '样本3的详细项目描述')";
                break;
                
            case "page3_data":
                insertSQL = "INSERT INTO " + tableName + " (product_name, price, inventory, category) VALUES "
                    + "('产品A', 99.99, 100, '电子产品'), "
                    + "('产品B', 199.99, 50, '家用电器'), "
                    + "('产品C', 299.99, 30, '电子产品')";
                break;
                
            case "page4_data":
                insertSQL = "INSERT INTO " + tableName + " (username, email, phone, address) VALUES "
                    + "('user1', 'user1@example.com', '13800138001', '北京市海淀区'), "
                    + "('user2', 'user2@example.com', '13800138002', '上海市浦东新区'), "
                    + "('user3', 'user3@example.com', '13800138003', '广州市天河区')";
                break;
                
            default:
                return;
        }
        
        try (Statement stmt = conn.createStatement()) {
            stmt.execute(insertSQL);
        }
    }
    
    /**
     * 查询数据 (不分页版本，向后兼容)
     */
    public static List<Map<String, Object>> queryData(String page, String searchParam) throws SQLException, ClassNotFoundException {
        return queryData(page, searchParam, 0, 0, null); // 不分页，无用户过滤
    }

    /**
     * 查询数据 (带分页)
     * @param page 页面标识
     * @param searchParam 搜索参数
     * @param pageNum 页码 (从1开始，0表示不分页)
     * @param pageSize 每页记录数 (0表示不分页)
     * @return 查询结果列表
     */
    public static List<Map<String, Object>> queryData(String page, String searchParam, int pageNum, int pageSize, String userId) throws SQLException, ClassNotFoundException {
        String tableName = PAGE_TABLES.getOrDefault(page, null);
        if (tableName == null) {
            return Collections.emptyList();
        }
        
        // 确保表存在，如果不存在则创建
        boolean tableReady = checkAndCreateTable(page);
        if (!tableReady) {
            throw new SQLException("表创建失败: " + tableName);
        }
        
        List<Map<String, Object>> resultList = new ArrayList<>();
        StringBuilder sql = new StringBuilder("SELECT * FROM " + tableName);
        
        // 对于page1和page2，添加用户ID过滤条件
        boolean needUserFilter = "reagent_list".equals(tableName) || "page2_data".equals(tableName);
        boolean hasWhere = false;
        
        
        if (needUserFilter && userId != null && !userId.trim().isEmpty()) {
            sql.append(" WHERE user_id = ?");
            hasWhere = true;
        }
        
        // 添加搜索条件（如果有）
        if (searchParam != null && !searchParam.trim().isEmpty()) {
            if (hasWhere) {
                sql.append(" AND (");
            } else {
                sql.append(" WHERE (");
                hasWhere = true;
            }
            
            switch (tableName) {
                case "reagent_list":
                    sql.append("material_name LIKE ? OR material_code LIKE ? OR expiry_date LIKE ?");
                    break;
                case "page2_data":
                    sql.append("use_time LIKE ? OR material_code LIKE ? OR group_names LIKE ?");
                    break;
                case "page3_data":
                    sql.append("product_name LIKE ? OR category LIKE ?");
                    break;
                case "page4_data":
                    sql.append("username LIKE ? OR email LIKE ? OR address LIKE ?");
                    break;
            }
            sql.append(")");
        }
        
        // 添加排序
        switch (tableName) {
            case "reagent_list":
                sql.append(" ORDER BY arrival_date ASC"); // 试剂列表按照到货日期正序排列
                break;
            case "page2_data":
                sql.append(" ORDER BY use_time DESC"); // 试剂使用列表按照使用时间倒序排列
                break;
            default:
                sql.append(" ORDER BY id DESC"); // 其他页面保持原来的id倒序
        }
        
        // 添加分页
        if (pageNum > 0 && pageSize > 0) {
            int offset = (pageNum - 1) * pageSize;
            sql.append(" LIMIT ").append(offset).append(", ").append(pageSize);
        }
        
        
        try (Connection conn = getConnection();
             PreparedStatement pstmt = conn.prepareStatement(sql.toString())) {
            
            // 设置用户ID参数（如果需要）
            if (needUserFilter && userId != null && !userId.trim().isEmpty()) {
                pstmt.setString(1, userId);
            }
            
            // 设置搜索参数
            if (searchParam != null && !searchParam.trim().isEmpty()) {
                String searchValue = "%" + searchParam + "%";
                int startIndex = (needUserFilter && userId != null && !userId.trim().isEmpty()) ? 2 : 1;
                switch (tableName) {
                    case "reagent_list":
                        pstmt.setString(startIndex, searchValue);
                        pstmt.setString(startIndex + 1, searchValue);
                        pstmt.setString(startIndex + 2, searchValue);
                        break;
                    case "page2_data":
                        pstmt.setString(startIndex, searchValue);
                        pstmt.setString(startIndex + 1, searchValue);
                        pstmt.setString(startIndex + 2, searchValue);
                        break;
                    case "page3_data":
                        pstmt.setString(startIndex, searchValue);
                        pstmt.setString(startIndex + 1, searchValue);
                        break;
                    case "page4_data":
                        pstmt.setString(startIndex, searchValue);
                        pstmt.setString(startIndex + 1, searchValue);
                        pstmt.setString(startIndex + 2, searchValue);
                        break;
                }
            }
            
            try (ResultSet rs = pstmt.executeQuery()) {
                ResultSetMetaData metaData = rs.getMetaData();
                int columnCount = metaData.getColumnCount();
                
                while (rs.next()) {
                    Map<String, Object> row = new HashMap<>();
                    for (int i = 1; i <= columnCount; i++) {
                        String columnName = metaData.getColumnName(i);
                        Object value = rs.getObject(i);
                        row.put(columnName, value);
                    }
                    resultList.add(row);
                }
            }
        }
        
        return resultList;
    }
    
    /**
     * 获取数据总数
     * @param page 页面标识
     * @param searchParam 搜索参数
     * @return 数据总数
     */
    public static int getDataCount(String page, String searchParam, String userId) throws SQLException, ClassNotFoundException {
        String tableName = PAGE_TABLES.getOrDefault(page, null);
        if (tableName == null) {
            return 0;
        }
        
        // 确保表存在，如果不存在则创建
        boolean tableReady = checkAndCreateTable(page);
        if (!tableReady) {
            throw new SQLException("表创建失败: " + tableName);
        }
        
        StringBuilder sql = new StringBuilder("SELECT COUNT(*) FROM " + tableName);
        
        // 对于page1和page2，添加用户ID过滤条件
        boolean needUserFilter = "reagent_list".equals(tableName) || "page2_data".equals(tableName);
        boolean hasWhere = false;
        
        
        if (needUserFilter && userId != null && !userId.trim().isEmpty()) {
            sql.append(" WHERE user_id = ?");
            hasWhere = true;
        }
        
        // 添加搜索条件（如果有）
        if (searchParam != null && !searchParam.trim().isEmpty()) {
            if (hasWhere) {
                sql.append(" AND (");
            } else {
                sql.append(" WHERE (");
                hasWhere = true;
            }
            
            switch (tableName) {
                case "reagent_list":
                    sql.append("material_name LIKE ? OR material_code LIKE ? OR expiry_date LIKE ?");
                    break;
                case "page2_data":
                    sql.append("use_time LIKE ? OR material_code LIKE ? OR group_names LIKE ?");
                    break;
                case "page3_data":
                    sql.append("product_name LIKE ? OR category LIKE ?");
                    break;
                case "page4_data":
                    sql.append("username LIKE ? OR email LIKE ? OR address LIKE ?");
                    break;
            }
            sql.append(")");
        }
        
        try (Connection conn = getConnection();
             PreparedStatement pstmt = conn.prepareStatement(sql.toString())) {
            
            // 设置用户ID参数（如果需要）
            if (needUserFilter && userId != null && !userId.trim().isEmpty()) {
                pstmt.setString(1, userId);
            }
            
            // 设置搜索参数
            if (searchParam != null && !searchParam.trim().isEmpty()) {
                String searchValue = "%" + searchParam + "%";
                int startIndex = (needUserFilter && userId != null && !userId.trim().isEmpty()) ? 2 : 1;
                switch (tableName) {
                    case "reagent_list":
                        pstmt.setString(startIndex, searchValue);
                        pstmt.setString(startIndex + 1, searchValue);
                        pstmt.setString(startIndex + 2, searchValue);
                        break;
                    case "page2_data":
                        pstmt.setString(startIndex, searchValue);
                        pstmt.setString(startIndex + 1, searchValue);
                        pstmt.setString(startIndex + 2, searchValue);
                        break;
                    case "page3_data":
                        pstmt.setString(startIndex, searchValue);
                        pstmt.setString(startIndex + 1, searchValue);
                        break;
                    case "page4_data":
                        pstmt.setString(startIndex, searchValue);
                        pstmt.setString(startIndex + 1, searchValue);
                        pstmt.setString(startIndex + 2, searchValue);
                        break;
                }
            }
            
            try (ResultSet rs = pstmt.executeQuery()) {
                if (rs.next()) {
                    return rs.getInt(1);
                }
            }
        }
        
        return 0;
    }

    /**
     * 从Excel导入数据，跳过重复的记录
     * @param page 页面标识
     * @param excelData Excel数据，每行代表一条记录
     * @return 包含导入结果的Map：成功导入数量和重复跳过数量
     */
    public static Map<String, Integer> importFromExcel(String page, List<Map<String, Object>> excelData, String userId) throws SQLException, ClassNotFoundException {
        // 调用改进的批量导入方法
        return batchImport(page, excelData, userId);
    }
    
    /**
     * 检查记录是否已存在（基于material_code、material_name字段组合和user_id）
     * @param conn 数据库连接
     * @param tableName 表名
     * @param materialCode 物料编码
     * @param materialName 物料名称
     * @param userId 用户ID
     * @return 如果存在则返回true，否则返回false
     */
    private static boolean checkDuplicate(Connection conn, String tableName, String materialCode, String materialName, String userId) throws SQLException {
        String sql = "SELECT COUNT(*) FROM " + tableName + " WHERE material_code = ? AND material_name = ? AND user_id = ?";
        
        try (PreparedStatement pstmt = conn.prepareStatement(sql)) {
            pstmt.setString(1, materialCode);
            pstmt.setString(2, materialName);
            pstmt.setString(3, userId);
            
            try (ResultSet rs = pstmt.executeQuery()) {
                if (rs.next()) {
                    int count = rs.getInt(1);
                    return count > 0;
                }
            }
        }
        
        return false;
    }
    
    /**
     * 添加数据
     */
    public static int addData(String page, Map<String, Object> data, String userId) throws SQLException, ClassNotFoundException {
        String tableName = PAGE_TABLES.getOrDefault(page, null);
        if (tableName == null) {
            return -1;
        }
        
        checkAndCreateTable(page); // 确保表存在
        
        // 对于page1和page2，自动添加user_id
        boolean needUserFilter = "reagent_list".equals(tableName) || "page2_data".equals(tableName);
        if (needUserFilter && userId != null && !userId.trim().isEmpty()) {
            data.put("user_id", userId);
        }
        
        StringBuilder columnNames = new StringBuilder();
        StringBuilder placeholders = new StringBuilder();
        List<Object> values = new ArrayList<>();
        
        boolean isFirst = true;
        for (Map.Entry<String, Object> entry : data.entrySet()) {
            // 跳过id、create_time和update_time字段
            if (entry.getKey().equals("id") || entry.getKey().equals("create_time") || entry.getKey().equals("update_time")) {
                continue;
            }
            
            if (!isFirst) {
                columnNames.append(", ");
                placeholders.append(", ");
            }
            columnNames.append(entry.getKey());
            placeholders.append("?");
            values.add(entry.getValue());
            isFirst = false;
        }
        
        String sql = "INSERT INTO " + tableName + " (" + columnNames + ") VALUES (" + placeholders + ")";
        
        try (Connection conn = getConnection();
             PreparedStatement pstmt = conn.prepareStatement(sql, Statement.RETURN_GENERATED_KEYS)) {
            
            // 重新构建字段名数组以便处理日期字段
            List<String> fieldNames = new ArrayList<>();
            for (Map.Entry<String, Object> entry : data.entrySet()) {
                if (!entry.getKey().equals("id") && !entry.getKey().equals("create_time") && !entry.getKey().equals("update_time")) {
                    fieldNames.add(entry.getKey());
                }
            }
            
            for (int i = 0; i < values.size(); i++) {
                Object value = values.get(i);
                // 处理特殊字段
                if (value instanceof String && ((String) value).trim().isEmpty()) {
                    String fieldName = fieldNames.get(i);
                    if (fieldName.contains("date") || fieldName.contains("time")) {
                        pstmt.setObject(i + 1, null);
                    } else if ("quantity".equals(fieldName)) {
                        // 数量字段的空字符串转为0.0（浮点数）
                        pstmt.setObject(i + 1, 0.0);
                    } else {
                        pstmt.setObject(i + 1, value);
                    }
                } else {
                    pstmt.setObject(i + 1, value);
                }
            }
            
            int affectedRows = pstmt.executeUpdate();
            if (affectedRows > 0) {
                try (ResultSet generatedKeys = pstmt.getGeneratedKeys()) {
                    if (generatedKeys.next()) {
                        return generatedKeys.getInt(1); // 返回新插入记录的ID
                    }
                }
            }
        }
        
        return -1;
    }
    
    /**
     * 更新数据
     */
    public static boolean updateData(String page, int id, Map<String, Object> data) throws SQLException, ClassNotFoundException {
        String tableName = PAGE_TABLES.getOrDefault(page, null);
        if (tableName == null) {
            return false;
        }
        
        StringBuilder setClause = new StringBuilder();
        List<Object> values = new ArrayList<>();
        
        boolean isFirst = true;
        for (Map.Entry<String, Object> entry : data.entrySet()) {
            // 跳过id、create_time和update_time字段
            if (entry.getKey().equals("id") || entry.getKey().equals("create_time") || entry.getKey().equals("update_time")) {
                continue;
            }
            
            if (!isFirst) {
                setClause.append(", ");
            }
            setClause.append(entry.getKey()).append(" = ?");
            values.add(entry.getValue());
            isFirst = false;
        }
        
        String sql = "UPDATE " + tableName + " SET " + setClause + " WHERE id = ?";
        values.add(id);
        
        try (Connection conn = getConnection();
             PreparedStatement pstmt = conn.prepareStatement(sql)) {
            
            // 重新构建字段名数组以便处理日期字段
            List<String> fieldNames = new ArrayList<>();
            for (Map.Entry<String, Object> entry : data.entrySet()) {
                if (!entry.getKey().equals("id") && !entry.getKey().equals("create_time") && !entry.getKey().equals("update_time")) {
                    fieldNames.add(entry.getKey());
                }
            }
            
            for (int i = 0; i < values.size(); i++) {
                Object value = values.get(i);
                // 最后一个值是ID，不需要特殊处理
                if (i == values.size() - 1) {
                    pstmt.setObject(i + 1, value);
                    continue;
                }
                
                // 处理特殊字段
                if (value instanceof String && ((String) value).trim().isEmpty()) {
                    String fieldName = fieldNames.get(i);
                    if (fieldName.contains("date") || fieldName.contains("time")) {
                        pstmt.setObject(i + 1, null);
                    } else if ("quantity".equals(fieldName)) {
                        // 数量字段的空字符串转为0.0（浮点数）
                        pstmt.setObject(i + 1, 0.0);
                    } else {
                        pstmt.setObject(i + 1, value);
                    }
                } else {
                    pstmt.setObject(i + 1, value);
                }
            }
            
            return pstmt.executeUpdate() > 0;
        }
    }
    
    /**
     * 删除数据
     */
    public static boolean deleteData(String page, int id) throws SQLException, ClassNotFoundException {
        String tableName = PAGE_TABLES.getOrDefault(page, null);
        if (tableName == null) {
            return false;
        }
        
        String sql = "DELETE FROM " + tableName + " WHERE id = ?";
        
        try (Connection conn = getConnection();
             PreparedStatement pstmt = conn.prepareStatement(sql)) {
            
            pstmt.setInt(1, id);
            
            return pstmt.executeUpdate() > 0;
        }
    }
    
    /**
     * 批量导入数据，跳过重复的记录
     * @param page 页面标识
     * @param dataList 数据列表
     * @return 包含导入结果的Map：成功导入数量和重复跳过数量
     */
    public static Map<String, Integer> batchImport(String page, List<Map<String, Object>> dataList, String userId) throws SQLException, ClassNotFoundException {
        String tableName = PAGE_TABLES.getOrDefault(page, null);
        if (tableName == null || dataList == null || dataList.isEmpty()) {
            Map<String, Integer> result = new HashMap<>();
            result.put("success", 0);
            result.put("duplicate", 0);
            return result;
        }
        
        checkAndCreateTable(page); // 确保表存在
        
        // 对于page1和page2，为每条数据添加user_id
        boolean needUserFilter = "reagent_list".equals(tableName) || "page2_data".equals(tableName);
        if (needUserFilter && userId != null && !userId.trim().isEmpty()) {
            for (Map<String, Object> data : dataList) {
                // 先移除可能存在的user_id，然后设置正确的值
                data.remove("user_id");
                data.put("user_id", userId);
            }
        }
        
        // 获取第一条数据的键作为列名
        Map<String, Object> firstData = dataList.get(0);
        StringBuilder columnNames = new StringBuilder();
        StringBuilder placeholders = new StringBuilder();
        
        List<String> columns = new ArrayList<>();
        for (String key : firstData.keySet()) {
            // 跳过id、create_time和update_time字段
            if (key.equals("id") || key.equals("create_time") || key.equals("update_time")) {
                continue;
            }
            columns.add(key);
        }
        
        boolean isFirst = true;
        for (String column : columns) {
            if (!isFirst) {
                columnNames.append(", ");
                placeholders.append(", ");
            }
            columnNames.append(column);
            placeholders.append("?");
            isFirst = false;
        }
        
        String sql = "INSERT INTO " + tableName + " (" + columnNames + ") VALUES (" + placeholders + ")";
        
        int successCount = 0;
        int duplicateCount = 0;
        
        try (Connection conn = getConnection()) {
            conn.setAutoCommit(false);
            
            try (PreparedStatement pstmt = conn.prepareStatement(sql)) {
                for (Map<String, Object> data : dataList) {
                    // 检查是否有物料编码和名称字段
                    String materialCode = null;
                    String materialName = null;
                    Object materialCodeObj = data.get("material_code");
                    Object materialNameObj = data.get("material_name");
                    
                    if (materialCodeObj != null && !materialCodeObj.toString().trim().isEmpty()) {
                        materialCode = materialCodeObj.toString().trim();
                    }
                    if (materialNameObj != null && !materialNameObj.toString().trim().isEmpty()) {
                        materialName = materialNameObj.toString().trim();
                    }
                    
                    // 如果有物料编码和名称且存在重复，则跳过该条记录（page2不需要重复检查）
                    if (materialCode != null && materialName != null && !page.equals("page2") && 
                        checkDuplicate(conn, tableName, materialCode, materialName, userId)) {
                        duplicateCount++;
                        continue;
                    }
                    
                    // 没有重复，准备插入
                    for (int i = 0; i < columns.size(); i++) {
                        String colName = columns.get(i);
                        Object value = data.get(colName);
                        
                        // 处理特殊字段的空值
                        if (value != null && value instanceof String) {
                            String strValue = ((String) value).trim();
                            if (strValue.isEmpty() && (colName.contains("date") || colName.contains("time"))) {
                                // 日期字段的空字符串转为null
                                pstmt.setObject(i + 1, null);
                            } else if (strValue.isEmpty() && "quantity".equals(colName)) {
                                // 数量字段的空字符串转为0.0（浮点数）
                                pstmt.setObject(i + 1, 0.0);
                            } else if (strValue.isEmpty()) {
                                // 其他字段保持空字符串
                                pstmt.setObject(i + 1, "");
                            } else {
                                pstmt.setObject(i + 1, value);
                            }
                        } else {
                            pstmt.setObject(i + 1, value);
                        }
                    }
                    
                    pstmt.addBatch();
                    successCount++;
                }
                
                // 如果没有需要插入的数据，直接提交并返回
                if (successCount == 0) {
                    conn.commit();
                    Map<String, Integer> result = new HashMap<>();
                    result.put("success", 0);
                    result.put("duplicate", duplicateCount);
                    return result;
                }
                
                // 执行批处理
                int[] results = pstmt.executeBatch();
                conn.commit();
                
                // 重新计算实际成功插入的数量
                successCount = 0;
                for (int result : results) {
                    if (result > 0) {
                        successCount++;
                    }
                }
            } catch (Exception e) {
                conn.rollback();
                // 添加详细的异常日志
                System.err.println("批量导入数据时发生异常:");
                System.err.println("表名: " + tableName);
                System.err.println("数据条数: " + dataList.size());
                System.err.println("用户ID: " + userId);
                System.err.println("异常类型: " + e.getClass().getSimpleName());
                System.err.println("异常信息: " + e.getMessage());
                e.printStackTrace();
                throw e;
            } finally {
                conn.setAutoCommit(true);
            }
        }
        
        Map<String, Integer> result = new HashMap<>();
        result.put("success", successCount);
        result.put("duplicate", duplicateCount);
        return result;
    }
    
    /**
     * 按ID查询单条数据
     * @param page 页面标识
     * @param id 记录ID
     * @return 查询结果列表
     */
    public static List<Map<String, Object>> getDataById(String page, int id) throws SQLException, ClassNotFoundException {
        String tableName = PAGE_TABLES.getOrDefault(page, null);
        if (tableName == null) {
            return Collections.emptyList();
        }
        
        // 确保表存在，如果不存在则创建
        boolean tableReady = checkAndCreateTable(page);
        if (!tableReady) {
            throw new SQLException("表创建失败: " + tableName);
        }
        
        List<Map<String, Object>> resultList = new ArrayList<>();
        String sql = "SELECT * FROM " + tableName + " WHERE id = ?";
        
        Connection conn = getConnection();
        try (PreparedStatement stmt = conn.prepareStatement(sql)) {
            stmt.setInt(1, id);
            ResultSet rs = stmt.executeQuery();
            
            while (rs.next()) {
                Map<String, Object> row = new HashMap<>();
                int columnCount = rs.getMetaData().getColumnCount();
                
                for (int i = 1; i <= columnCount; i++) {
                    String columnName = rs.getMetaData().getColumnName(i);
                    Object value = rs.getObject(i);
                    row.put(columnName, value);
                }
                
                resultList.add(row);
            }
        } finally {
            conn.close();
        }
        
        return resultList;
    }
} 